package com.mytool.service;

import com.mytool.model.ConfigParam;
import com.opencsv.CSVParserBuilder;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import com.opencsv.exceptions.CsvValidationException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Service
@Slf4j
public class CsvParserService {

    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    /**
     * 解析CSV文件并转换为ConfigParam对象列表
     */
    public List<ConfigParam> parseConfigParamCsv(String filePath) throws IOException, CsvValidationException {
        Path path = Paths.get(filePath);
        List<ConfigParam> configParams = new ArrayList<>();

        // 尝试多种编码
        List<Charset> charsets = List.of(
                StandardCharsets.UTF_8,
                Charset.forName("GBK"),
                Charset.forName("GB2312"),
                Charset.forName("GB18030"),
                StandardCharsets.ISO_8859_1
        );

        IOException lastException = null;
        
        // 尝试不同编码
        for (Charset charset : charsets) {
            try (InputStreamReader reader = new InputStreamReader(Files.newInputStream(path), charset);
                 CSVReader csvReader = new CSVReaderBuilder(reader)
                         .withCSVParser(new CSVParserBuilder().withSeparator('\t').build())
                         .build()) {

                // 跳过标题行
                String[] headers = csvReader.readNext();
                if (headers == null) {
                    continue;
                }

                configParams.clear();
                String[] line;
                while ((line = csvReader.readNext()) != null) {
                    if (line.length < 14) continue; // 确保行数据完整
                    
                    try {
                        ConfigParam param = new ConfigParam();
                        param.setId(Long.parseLong(line[0]));
                        param.setParamKey(removeQuotes(line[1]));
                        param.setParamValue(removeQuotes(line[2]));
                        param.setDescription(removeQuotes(line[3]));
                        param.setModule(removeQuotes(line[4]));
                        param.setParamType(Integer.parseInt(line[5]));
                        param.setIsBuiltin(Integer.parseInt(line[6]) == 1);
                        param.setSortOrder(Integer.parseInt(line[7]));
                        
                        param.setCreateTime(parseDateTime(removeQuotes(line[8])));
                        param.setUpdateTime(parseDateTime(removeQuotes(line[9])));
                        
                        param.setCreateBy("(null)".equals(line[10]) ? null : line[10]);
                        param.setUpdateBy("(null)".equals(line[11]) ? null : line[11]);
                        
                        param.setIsDeleted(Integer.parseInt(line[12]) == 1);
                        param.setVersion(Integer.parseInt(line[13]));
                        
                        configParams.add(param);
                    } catch (Exception e) {
                        log.debug("解析行数据出错: {}", e.getMessage());
                        // 继续处理下一行
                    }
                }
                
                if (!configParams.isEmpty()) {
                    log.info("成功使用编码 {} 解析CSV文件", charset);
                    return configParams;
                }
            } catch (IOException e) {
                lastException = e;
                log.debug("使用编码 {} 解析CSV文件失败: {}", charset, e.getMessage());
            }
        }
        
        if (lastException != null) {
            throw lastException;
        }

        return configParams;
    }
    
    private String removeQuotes(String value) {
        if (value == null) return null;
        return value.replaceAll("^\"|\"$", "");
    }
    
    private LocalDateTime parseDateTime(String dateStr) {
        if (dateStr == null || dateStr.isEmpty() || "(null)".equals(dateStr)) {
            return null;
        }
        return LocalDateTime.parse(dateStr, DATE_FORMATTER);
    }

    /**
     * 生成建表DDL语句
     */
    public String generateDdl() {
        StringBuilder ddl = new StringBuilder();
        ddl.append("CREATE TABLE config_param (\n");
        ddl.append("    id BIGINT NOT NULL PRIMARY KEY,\n");
        ddl.append("    param_key VARCHAR(100) NOT NULL COMMENT '参数键',\n");
        ddl.append("    param_value VARCHAR(500) NOT NULL COMMENT '参数值',\n");
        ddl.append("    description VARCHAR(200) COMMENT '描述',\n");
        ddl.append("    module VARCHAR(50) COMMENT '所属模块',\n");
        ddl.append("    param_type INT NOT NULL DEFAULT 0 COMMENT '参数类型：0-字符串，1-数字，2-布尔值',\n");
        ddl.append("    is_builtin TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否内置参数',\n");
        ddl.append("    sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',\n");
        ddl.append("    create_time DATETIME COMMENT '创建时间',\n");
        ddl.append("    update_time DATETIME COMMENT '更新时间',\n");
        ddl.append("    create_by VARCHAR(50) COMMENT '创建人',\n");
        ddl.append("    update_by VARCHAR(50) COMMENT '更新人',\n");
        ddl.append("    is_deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否删除',\n");
        ddl.append("    version INT NOT NULL DEFAULT 1 COMMENT '版本号'\n");
        ddl.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置参数表';\n\n");
        
        ddl.append("CREATE UNIQUE INDEX uk_param_key ON config_param(param_key);\n");
        ddl.append("CREATE INDEX idx_module ON config_param(module);\n");
        
        return ddl.toString();
    }

    /**
     * 生成插入数据的DML语句
     */
    public String generateDml(List<ConfigParam> configParams) {
        if (configParams == null || configParams.isEmpty()) {
            return "";
        }

        StringBuilder dml = new StringBuilder();
        dml.append("INSERT INTO config_param (\n");
        dml.append("    id, param_key, param_value, description, module, param_type, is_builtin,\n");
        dml.append("    sort_order, create_time, update_time, create_by, update_by, is_deleted, version\n");
        dml.append(") VALUES\n");

        for (int i = 0; i < configParams.size(); i++) {
            ConfigParam param = configParams.get(i);
            dml.append("(");
            dml.append(param.getId()).append(", ");
            dml.append("'").append(escapeSQL(param.getParamKey())).append("', ");
            dml.append("'").append(escapeSQL(param.getParamValue())).append("', ");
            dml.append("'").append(escapeSQL(param.getDescription())).append("', ");
            dml.append("'").append(escapeSQL(param.getModule())).append("', ");
            dml.append(param.getParamType()).append(", ");
            dml.append(param.getIsBuiltin() ? 1 : 0).append(", ");
            dml.append(param.getSortOrder()).append(", ");
            
            dml.append(formatDateTime(param.getCreateTime())).append(", ");
            dml.append(formatDateTime(param.getUpdateTime())).append(", ");
            
            dml.append(param.getCreateBy() == null ? "NULL" : "'" + escapeSQL(param.getCreateBy()) + "'").append(", ");
            dml.append(param.getUpdateBy() == null ? "NULL" : "'" + escapeSQL(param.getUpdateBy()) + "'").append(", ");
            
            dml.append(param.getIsDeleted() ? 1 : 0).append(", ");
            dml.append(param.getVersion());
            dml.append(")");
            
            if (i < configParams.size() - 1) {
                dml.append(",\n");
            } else {
                dml.append(";\n");
            }
        }

        return dml.toString();
    }
    
    private String formatDateTime(LocalDateTime dateTime) {
        if (dateTime == null) {
            return "NULL";
        }
        return "'" + dateTime.format(DATE_FORMATTER) + "'";
    }
    
    private String escapeSQL(String input) {
        if (input == null) {
            return "";
        }
        return input.replace("'", "''");
    }
} 